<?php

/**
 * 信息模型
 */
if ( !defined( 'BASEPATH' ) ) exit( 'No direct script access allowed' );

class Post_model extends MY_Model
{

    //信息类型
    private $_postType = array(
        //出租
        'rent' => 1,
        //求租
        'needrent' => 2,
        //卖房
        'sell' => 3,
        //买房
        'buy' => 4,
    );
    public $_table = array(
        'post' => 'post',
        'common' => 'common',
        'user' => 'user',
    );
    public $_fields = array(
        'post' => array(
            'id' => 'id',
            'title' => 'title',
            'zone' => 'zone',
            'amount' => 'amount',
            'year' => 'year',
            'month' => 'month',
            'day' => 'day',
            'addtime' => 'addtime',
            'road' => 'road',
            'content' => 'content',
            'room' => 'room',
            'floor' => 'floor',
            'userid' => 'userid',
            'type' => 'type',
            'ispersonal' => 'ispersonal',
        ),
        'common' => array(
            'id' => 'id',
            'name' => 'name',
            'type' => 'type',
        ),
        'user' => array(
            'id' => 'id',
            'name' => 'name',
        ),
    );

    /**
     * 插入新房产信息
     * @param array $data
     * @return int
     */
    public function add( $data )
    {
        $insertData = array(
            'title' => $data['title'],
            'zone' => $data['zone'],
            'amount' => $data['amount'],
            'year' => date( 'Y' ),
            'month' => date( 'm' ),
            'day' => date( 'd' ),
            'addtime' => time(),
            'road' => $data['road'],
            'content' => $data['content'],
            'room' => $data['room'],
            'floor' => $data['floor'],
            'square' => $data['square'],
            'userid' => $data['userid'],
            'type' => $data['type'],
            'ispersonal' => intval( $data['ispersonal'] ),
            'updatetime' => time(),
        );
        $this->db->insert( $this->_table['post'], $insertData );
        return $this->insert_id();
    }

    public function update( $data, $postId )
    {
        $updateData = array(
            'title' => $data['title'],
            'zone' => $data['zone'],
            'amount' => $data['amount'],
            'road' => $data['road'],
            'content' => $data['content'],
            'room' => $data['room'],
            'floor' => $data['floor'],
            'square' => $data['square'],
            'userid' => $data['userid'],
            'type' => $data['type'],
            'ispersonal' => intval( $data['ispersonal'] ),
            'updatetime' => time(),
        );
        $this->db->where('id', $postId)->update( $this->_table['post'], $updateData );
        return $this->affected_rows();
    }
    
    /**
     * 获取信息表数据总数
     * @return int 
     */
    public function rentTotal()
    {
        return $this->db->from( $this->_table['post'] )
                        ->where( 'visible', 1 )
                        ->where( 'type', $this->_postType['rent'] )
                        ->count_all_results();
    }

    /**
     * 获取某个地区的租房信息总数量
     * @param int $zoneId 区域ID
     * @return int 数量
     */
    public function rentTotalByZoneId( $zoneId )
    {
        return $this->db->from( $this->_table['post'] )
                        ->where( 'visible', 1 )
                        ->where( 'zone', $zoneId )
                        ->where( 'type', $this->_postType['rent'] )
                        ->count_all_results();
    }

    /**
     * 获取某个地区的卖房信息总数量
     * @param int $zoneId 区域ID
     * @return int 数量
     */
    public function sellTotalByZoneId( $zoneId )
    {
        return $this->db->from( $this->_table['post'] )
                        ->where( 'visible', 1 )
                        ->where( 'zone', $zoneId )
                        ->where( 'type', $this->_postType['buy'] )
                        ->count_all_results();
    }
    
    /**
     * 根据关键字获取出租数据条数
     * @param type $keyword
     * @return type 
     */
    public function rentTotalByKeyword( $keyword )
    {
        return $this->db->from( $this->_table['post'] )
                        ->where( 'visible', 1 )
                        ->like( 'title', $keyword )
                        ->where( 'type', $this->_postType['rent'] )
                        ->count_all_results();
    }

    /**
     * 获取某个金额范围信息总数量
     * @param int $lowPrice 下限
     * @param int $highPrice 上限
     * @return int 数量
     */
    public function rentTotalByPrice( $lowPrice, $highPrice = '' )
    {
        $result = $this->db->from( $this->_table['post'] )
                ->where( 'visible', 1 )
                ->where( 'type', $this->_postType['rent'] );
        if ( $highPrice === NULL )
        {
            $result->where( 'amount >=', $lowPrice );
        }
        else
        {
            $result->where( 'amount >=', $lowPrice )->where( 'amount <', $highPrice );
        }
        return $result->count_all_results();
    }
    
    /**
     * 获取某个金额范围信息总数量
     * @param int $lowPrice 下限
     * @param int $highPrice 上限
     * @return int 数量
     */
    public function sellTotalByPrice( $lowPrice, $highPrice = '' )
    {
        $result = $this->db->from( $this->_table['post'] )
                ->where( 'visible', 1 )
                ->where( 'type', $this->_postType['buy'] );
        if ( $highPrice === NULL )
        {
            $result->where( 'amount >=', $lowPrice );
        }
        else
        {
            $result->where( 'amount >=', $lowPrice )->where( 'amount <', $highPrice );
        }
        return $result->count_all_results();
    }

    /**
     * 获取某个厅室的租房信息总数量
     * @param int $roomId 厅室ID
     * @return int
     */
    public function rentTotalByRoomId( $roomId )
    {
        return $this->db->from( $this->_table['post'] )
                        ->where( 'visible', 1 )
                        ->where( 'room', $roomId )
                        ->where( 'type', $this->_postType['rent'] )
                        ->count_all_results();
    }
    
    /**
     * 获取某个厅室的卖房信息总数量
     * @param int $roomId 厅室ID
     * @return int
     */
    public function sellTotalByRoomId( $roomId )
    {
        return $this->db->from( $this->_table['post'] )
                        ->where( 'visible', 1 )
                        ->where( 'room', $roomId )
                        ->where( 'type', $this->_postType['buy'] )
                        ->count_all_results();
    }

    /**
     * 根据信息ID获取信息具体数据
     * @param int $postId 信息ID
     * @return array
     */
    public function getById( $postId )
    {
        return $this->db->select( 'p.id,p.title,p.zone,p.square,p.amount,p.addtime,p.road,p.room,p.type,p.ispersonal,p.content,p.userid,p.floor,p.updatetime,u.name as publisher,c1.name as zonename,c2.name as roomname' )
                        ->from( "{$this->_table['post']} as p" )
                        ->join( "{$this->_table['user']} as u", 'u.id=p.userid' )
                        ->join( "{$this->_table['common']} as c1", 'c1.id=p.zone' )
                        ->join( "{$this->_table['common']} as c2", 'c2.id=p.room' )
                        ->where( 'p.visible', 1 )
                        ->where( 'p.id', $postId )
                        ->get()
                        ->row_array();
    }

    /**
     * 根据区域ID获取相应的出租房源信息
     * @param int $zoneId 区域ID
     * @param int $limit 查询条数
     * @return array
     */
    public function rentGetByZone( $zoneId, $limit = 0 )
    {
        return $this->db->select( 'p.id,p.title,p.zone,p.amount,p.addtime,p.road,p.room,p.type,p.ispersonal,p.userid,p.floor,p.updatetime,u.name as publisher,c1.name as zonename,c2.name as roomname' )
                        ->from( "{$this->_table['post']} as p" )
                        ->join( "{$this->_table['user']} as u", 'u.id=p.userid' )
                        ->join( "{$this->_table['common']} as c1", 'c1.id=p.zone' )
                        ->join( "{$this->_table['common']} as c2", 'c2.id=p.room' )
                        ->where( 'p.visible', 1 )
                        ->where( 'p.zone', $zoneId )
                        ->where( 'p.type', $this->_postType['rent'] )
                        ->limit( $limit )
                        ->get()
                        ->result_array();
    }

    /**
     * 查询所有可见的信息
     * @param int $perPage 每页显示条数
     * @param inte $offset 游标
     * @return array
     */
    public function rentAll( $perPage = 15, $offset = 0 )
    {
        return $this->db->select( 'p.id,p.title,p.zone,p.amount,p.addtime,p.road,p.room,p.type,p.ispersonal,p.updatetime,c1.name as zonename,c2.name as roomname' )
                        ->from( "{$this->_table['post']} as p" )
                        ->join( "{$this->_table['common']} as c1", 'c1.id=p.zone' )
                        ->join( "{$this->_table['common']} as c2", 'c2.id=p.room' )
                        ->where( 'p.visible', 1 )
                        ->where( 'p.type', $this->_postType['rent'] )
                        ->order_by( 'p.updatetime', 'desc' )
                        ->limit( $perPage, $offset )
                        ->get()
                        ->result_array();
    }

    /**
     * 查询所有可见的信息
     * @param int $perPage 每页显示条数
     * @param inte $offset 游标
     * @return array
     */
    public function sellAll( $perPage = 15, $offset = 0 )
    {
        return $this->db->select( 'p.id,p.title,p.zone,p.amount,p.addtime,p.road,p.room,p.type,p.ispersonal,p.updatetime,c1.name as zonename,c2.name as roomname' )
                        ->from( "{$this->_table['post']} as p" )
                        ->join( "{$this->_table['common']} as c1", 'c1.id=p.zone' )
                        ->join( "{$this->_table['common']} as c2", 'c2.id=p.room' )
                        ->where( 'p.visible', 1 )
                        ->where( 'p.type', $this->_postType['buy'] )
                        ->order_by( 'p.updatetime', 'desc' )
                        ->limit( $perPage, $offset )
                        ->get()
                        ->result_array();
    }
    
    /**
     * 根据区域ID查询所有可见的信息
     * @param int $zoneId 区域ID
     * @param int $perPage 每页显示条数
     * @param inte $offset 游标
     * @return array
     */
    public function rentAllByZoneId( $zoneId, $perPage = 15, $offset = 0 )
    {
        return $this->db->select( 'p.id,p.title,p.zone,p.amount,p.addtime,p.road,p.room,p.type,p.ispersonal,p.updatetime,c1.name as zonename,c2.name as roomname' )
                        ->from( "{$this->_table['post']} as p" )
                        ->join( "{$this->_table['common']} as c1", 'c1.id=p.zone' )
                        ->join( "{$this->_table['common']} as c2", 'c2.id=p.room' )
                        ->where( 'p.visible', 1 )
                        ->where( 'p.zone', $zoneId )
                        ->where( 'p.type', $this->_postType['rent'] )
                        ->order_by( 'p.updatetime', 'desc' )
                        ->limit( $perPage, $offset )
                        ->get()
                        ->result_array();
    }
    
    /**
     * 根据区域ID查询所有可见的信息
     * @param int $zoneId 区域ID
     * @param int $perPage 每页显示条数
     * @param inte $offset 游标
     * @return array
     */
    public function sellAllByZoneId( $zoneId, $perPage = 15, $offset = 0 )
    {
        return $this->db->select( 'p.id,p.title,p.zone,p.amount,p.addtime,p.road,p.room,p.type,p.ispersonal,p.updatetime,c1.name as zonename,c2.name as roomname' )
                        ->from( "{$this->_table['post']} as p" )
                        ->join( "{$this->_table['common']} as c1", 'c1.id=p.zone' )
                        ->join( "{$this->_table['common']} as c2", 'c2.id=p.room' )
                        ->where( 'p.visible', 1 )
                        ->where( 'p.zone', $zoneId )
                        ->where( 'p.type', $this->_postType['buy'] )
                        ->order_by( 'p.updatetime', 'desc' )
                        ->limit( $perPage, $offset )
                        ->get()
                        ->result_array();
    }

    /**
     * 获取某个金额范围的出租信息
     * @param int $lowPrice 
     * @param int $highPrice
     * @param int $perPage
     * @param int $offset
     * @return array
     */
    public function rentAllByPrice( $lowPrice, $highPrice = '', $perPage = 15, $offset = 0 )
    {
        $result = $this->db->select( 'p.id,p.title,p.zone,p.amount,p.addtime,p.road,p.room,p.type,p.ispersonal,p.updatetime,c1.name as zonename,c2.name as roomname' )
                ->from( "{$this->_table['post']} as p" )
                ->join( "{$this->_table['common']} as c1", 'c1.id=p.zone' )
                ->join( "{$this->_table['common']} as c2", 'c2.id=p.room' )
                ->where( 'p.visible', 1 )
                ->where( 'p.type', $this->_postType['rent'] );
        if ( $highPrice === NULL )
        {
            $result->where( 'p.amount >=', $lowPrice );
        }
        else
        {
            $result->where( 'p.amount >=', $lowPrice )->where( 'p.amount <', $highPrice );
        }
        return $result->order_by( 'p.updatetime', 'desc' )
                        ->limit( $perPage, $offset )
                        ->get()
                        ->result_array();
    }
    
    /**
     * 获取某个金额范围的出租信息
     * @param int $lowPrice 
     * @param int $highPrice
     * @param int $perPage
     * @param int $offset
     * @return array
     */
    public function sellAllByPrice( $lowPrice, $highPrice = '', $perPage = 15, $offset = 0 )
    {
        $result = $this->db->select( 'p.id,p.title,p.zone,p.amount,p.addtime,p.road,p.room,p.type,p.ispersonal,p.updatetime,c1.name as zonename,c2.name as roomname' )
                ->from( "{$this->_table['post']} as p" )
                ->join( "{$this->_table['common']} as c1", 'c1.id=p.zone' )
                ->join( "{$this->_table['common']} as c2", 'c2.id=p.room' )
                ->where( 'p.visible', 1 )
                ->where( 'p.type', $this->_postType['buy'] );
        if ( $highPrice === NULL )
        {
            $result->where( 'p.amount >=', $lowPrice );
        }
        else
        {
            $result->where( 'p.amount >=', $lowPrice )->where( 'p.amount <', $highPrice );
        }
        return $result->order_by( 'p.updatetime', 'desc' )
                        ->limit( $perPage, $offset )
                        ->get()
                        ->result_array();
    }

    /**
     * 根据厅室ID查询所有可见的信息
     * @param int $roomId 厅室ID
     * @param int $perPage 每页显示条数
     * @param inte $offset 游标
     * @return array
     */
    public function rentAllByRoomId( $roomId, $perPage = 15, $offset = 0 )
    {
        return $this->db->select( 'p.id,p.title,p.zone,p.amount,p.addtime,p.road,p.room,p.type,p.ispersonal,p.updatetime,c1.name as zonename,c2.name as roomname' )
                        ->from( "{$this->_table['post']} as p" )
                        ->join( "{$this->_table['common']} as c1", 'c1.id=p.zone' )
                        ->join( "{$this->_table['common']} as c2", 'c2.id=p.room' )
                        ->where( 'p.visible', 1 )
                        ->where( 'p.room', $roomId )
                        ->where( 'p.type', $this->_postType['rent'] )
                        ->order_by( 'p.updatetime', 'desc' )
                        ->limit( $perPage, $offset )
                        ->get()
                        ->result_array();
    }

    /**
     * 根据厅室ID查询所有可见的信息
     * @param int $roomId 厅室ID
     * @param int $perPage 每页显示条数
     * @param inte $offset 游标
     * @return array
     */
    public function sellAllByRoomId( $roomId, $perPage = 15, $offset = 0 )
    {
        return $this->db->select( 'p.id,p.title,p.zone,p.amount,p.addtime,p.road,p.room,p.type,p.ispersonal,p.updatetime,c1.name as zonename,c2.name as roomname' )
                        ->from( "{$this->_table['post']} as p" )
                        ->join( "{$this->_table['common']} as c1", 'c1.id=p.zone' )
                        ->join( "{$this->_table['common']} as c2", 'c2.id=p.room' )
                        ->where( 'p.visible', 1 )
                        ->where( 'p.room', $roomId )
                        ->where( 'p.type', $this->_postType['buy'] )
                        ->order_by( 'p.updatetime', 'desc' )
                        ->limit( $perPage, $offset )
                        ->get()
                        ->result_array();
    }
    
    /**
     * 获取个人房源信息
     * @param int $perPage 每页显示条数
     * @param inte $offset 游标
     * @return array
     */
    public function rentPersonal( $perPage = 15, $offset = 0 )
    {
        return $this->db->select( 'p.id,p.title,p.zone,p.amount,p.addtime,p.road,p.room,p.type,p.ispersonal,p.updatetime,c1.name as zonename,c2.name as roomname' )
                        ->from( "{$this->_table['post']} as p" )
                        ->join( "{$this->_table['common']} as c1", 'c1.id=p.zone' )
                        ->join( "{$this->_table['common']} as c2", 'c2.id=p.room' )
                        ->where( 'p.visible', 1 )
                        ->where( 'p.ispersonal', 1 )
                        ->where( 'p.type', $this->_postType['rent'] )
                        ->order_by( 'p.updatetime', 'desc' )
                        ->limit( $perPage, $offset )
                        ->get()
                        ->result_array();
    }

    /**
     * 根据区域ID获取个人出租房源信息
     * @param int $zoneId 区域ID
     * @param int $perPage 每页显示条数
     * @param inte $offset 游标
     * @return array
     */
    public function rentPersonalByZoneId( $zoneId, $perPage = 15, $offset = 0 )
    {
        return $this->db->select( 'p.id,p.title,p.zone,p.amount,p.addtime,p.road,p.room,p.type,p.ispersonal,p.updatetime,c1.name as zonename,c2.name as roomname' )
                        ->from( "{$this->_table['post']} as p" )
                        ->join( "{$this->_table['common']} as c1", 'c1.id=p.zone' )
                        ->join( "{$this->_table['common']} as c2", 'c2.id=p.room' )
                        ->where( 'p.visible', 1 )
                        ->where( 'p.ispersonal', 1 )
                        ->where( 'p.zone', $zoneId )
                        ->where( 'p.type', $this->_postType['rent'] )
                        ->order_by( 'p.updatetime', 'desc' )
                        ->limit( $perPage, $offset )
                        ->get()
                        ->result_array();
    }
    
    /**
     * 根据区域ID获取个人卖房源信息
     * @param int $zoneId 区域ID
     * @param int $perPage 每页显示条数
     * @param inte $offset 游标
     * @return array
     */
    public function sellPersonalByZoneId( $zoneId, $perPage = 15, $offset = 0 )
    {
        return $this->db->select( 'p.id,p.title,p.zone,p.amount,p.addtime,p.road,p.room,p.type,p.ispersonal,p.updatetime,c1.name as zonename,c2.name as roomname' )
                        ->from( "{$this->_table['post']} as p" )
                        ->join( "{$this->_table['common']} as c1", 'c1.id=p.zone' )
                        ->join( "{$this->_table['common']} as c2", 'c2.id=p.room' )
                        ->where( 'p.visible', 1 )
                        ->where( 'p.ispersonal', 1 )
                        ->where( 'p.zone', $zoneId )
                        ->where( 'p.type', $this->_postType['buy'] )
                        ->order_by( 'p.updatetime', 'desc' )
                        ->limit( $perPage, $offset )
                        ->get()
                        ->result_array();
    }

    /**
     * 根据金额范围获取所有个人出租信息
     * @param int $lowPrice
     * @param int $highPrice
     * @param int $perPage
     * @param int $offset
     * @return array
     */
    public function rentPersonalByPrice( $lowPrice, $highPrice, $perPage = 15, $offset = 0 )
    {
        $result = $this->db->select( 'p.id,p.title,p.zone,p.amount,p.addtime,p.road,p.room,p.type,p.ispersonal,p.updatetime,c1.name as zonename,c2.name as roomname' )
                ->from( "{$this->_table['post']} as p" )
                ->join( "{$this->_table['common']} as c1", 'c1.id=p.zone' )
                ->join( "{$this->_table['common']} as c2", 'c2.id=p.room' )
                ->where( 'p.visible', 1 )
                ->where( 'p.ispersonal', 1 )
                ->where( 'p.type', $this->_postType['rent'] );
        if ( $highPrice === NULL )
        {
            $result->where( 'p.amount >=', $lowPrice );
        }
        else
        {
            $result->where( 'p.amount >=', $lowPrice )->where( 'p.amount <', $highPrice );
        }
        return $result->order_by( 'p.updatetime', 'desc' )
                        ->limit( $perPage, $offset )
                        ->get()
                        ->result_array();
    }
    
    /**
     * 根据金额范围获取所有个人出租信息
     * @param int $lowPrice
     * @param int $highPrice
     * @param int $perPage
     * @param int $offset
     * @return array
     */
    public function sellPersonalByPrice( $lowPrice, $highPrice, $perPage = 15, $offset = 0 )
    {
        $result = $this->db->select( 'p.id,p.title,p.zone,p.amount,p.addtime,p.road,p.room,p.type,p.ispersonal,p.updatetime,c1.name as zonename,c2.name as roomname' )
                ->from( "{$this->_table['post']} as p" )
                ->join( "{$this->_table['common']} as c1", 'c1.id=p.zone' )
                ->join( "{$this->_table['common']} as c2", 'c2.id=p.room' )
                ->where( 'p.visible', 1 )
                ->where( 'p.ispersonal', 1 )
                ->where( 'p.type', $this->_postType['buy'] );
        if ( $highPrice === NULL )
        {
            $result->where( 'p.amount >=', $lowPrice );
        }
        else
        {
            $result->where( 'p.amount >=', $lowPrice )->where( 'p.amount <', $highPrice );
        }
        return $result->order_by( 'p.updatetime', 'desc' )
                        ->limit( $perPage, $offset )
                        ->get()
                        ->result_array();
    }

    /**
     * 根据厅室ID获取个人出租房源信息
     * @param int $roomId 厅室ID
     * @param int $perPage 每页显示条数
     * @param inte $offset 游标
     * @return array
     */
    public function rentPersonalByRoomId( $roomId, $perPage = 15, $offset = 0 )
    {
        return $this->db->select( 'p.id,p.title,p.zone,p.amount,p.addtime,p.road,p.room,p.type,p.ispersonal,p.updatetime,c1.name as zonename,c2.name as roomname' )
                        ->from( "{$this->_table['post']} as p" )
                        ->join( "{$this->_table['common']} as c1", 'c1.id=p.zone' )
                        ->join( "{$this->_table['common']} as c2", 'c2.id=p.room' )
                        ->where( 'p.visible', 1 )
                        ->where( 'p.ispersonal', 1 )
                        ->where( 'p.room', $roomId )
                        ->where( 'p.type', $this->_postType['rent'] )
                        ->order_by( 'p.updatetime', 'desc' )
                        ->limit( $perPage, $offset )
                        ->get()
                        ->result_array();
    }

    /**
     * 根据厅室ID获取个人卖房源信息
     * @param int $roomId 厅室ID
     * @param int $perPage 每页显示条数
     * @param inte $offset 游标
     * @return array
     */
    public function sellPersonalByRoomId( $roomId, $perPage = 15, $offset = 0 )
    {
        return $this->db->select( 'p.id,p.title,p.zone,p.amount,p.addtime,p.road,p.room,p.type,p.ispersonal,p.updatetime,c1.name as zonename,c2.name as roomname' )
                        ->from( "{$this->_table['post']} as p" )
                        ->join( "{$this->_table['common']} as c1", 'c1.id=p.zone' )
                        ->join( "{$this->_table['common']} as c2", 'c2.id=p.room' )
                        ->where( 'p.visible', 1 )
                        ->where( 'p.ispersonal', 1 )
                        ->where( 'p.room', $roomId )
                        ->where( 'p.type', $this->_postType['buy'] )
                        ->order_by( 'p.updatetime', 'desc' )
                        ->limit( $perPage, $offset )
                        ->get()
                        ->result_array();
    }
    
    /**
     * 获取中介房源信息
     * @param int $perPage 每页显示条数
     * @param inte $offset 游标
     * @return array
     */
    public function rentAgent( $perPage = 15, $offset = 0 )
    {
        return $this->db->select( 'p.id,p.title,p.zone,p.amount,p.addtime,p.road,p.room,p.type,p.ispersonal,p.updatetime,c1.name as zonename,c2.name as roomname' )
                        ->from( "{$this->_table['post']} as p" )
                        ->join( "{$this->_table['common']} as c1", 'c1.id=p.zone' )
                        ->join( "{$this->_table['common']} as c2", 'c2.id=p.room' )
                        ->where( 'p.visible', 1 )
                        ->where( 'p.ispersonal', 0 )
                        ->where( 'p.type', $this->_postType['rent'] )
                        ->order_by( 'p.updatetime', 'desc' )
                        ->limit( $perPage, $offset )
                        ->get()
                        ->result_array();
    }

    /**
     * 根据区域ID获取中介出租房源信息
     * @param int $zoneId 区域ID
     * @param int $perPage 每页显示条数
     * @param inte $offset 游标
     * @return array
     */
    public function rentAgentByZoneId( $zoneId, $perPage = 15, $offset = 0 )
    {
        return $this->db->select( 'p.id,p.title,p.zone,p.amount,p.addtime,p.road,p.room,p.type,p.ispersonal,p.updatetime,c1.name as zonename,c2.name as roomname' )
                        ->from( "{$this->_table['post']} as p" )
                        ->join( "{$this->_table['common']} as c1", 'c1.id=p.zone' )
                        ->join( "{$this->_table['common']} as c2", 'c2.id=p.room' )
                        ->where( 'p.visible', 1 )
                        ->where( 'p.ispersonal', 0 )
                        ->where( 'p.zone', $zoneId )
                        ->where( 'p.type', $this->_postType['rent'] )
                        ->order_by( 'p.updatetime', 'desc' )
                        ->limit( $perPage, $offset )
                        ->get()
                        ->result_array();
    }

    /**
     * 根据区域ID获取中介卖房源信息
     * @param int $zoneId 区域ID
     * @param int $perPage 每页显示条数
     * @param inte $offset 游标
     * @return array
     */
    public function sellAgentByZoneId( $zoneId, $perPage = 15, $offset = 0 )
    {
        return $this->db->select( 'p.id,p.title,p.zone,p.amount,p.addtime,p.road,p.room,p.type,p.ispersonal,p.updatetime,c1.name as zonename,c2.name as roomname' )
                        ->from( "{$this->_table['post']} as p" )
                        ->join( "{$this->_table['common']} as c1", 'c1.id=p.zone' )
                        ->join( "{$this->_table['common']} as c2", 'c2.id=p.room' )
                        ->where( 'p.visible', 1 )
                        ->where( 'p.ispersonal', 0 )
                        ->where( 'p.zone', $zoneId )
                        ->where( 'p.type', $this->_postType['buy'] )
                        ->order_by( 'p.updatetime', 'desc' )
                        ->limit( $perPage, $offset )
                        ->get()
                        ->result_array();
    }
    
    /**
     * 根据金额范围获取所有中介出租信息
     * @param int $lowPrice
     * @param int $highPrice
     * @param int $perPage
     * @param int $offset
     * @return array
     */
    public function rentAgentByPrice( $lowPrice, $highPrice, $perPage = 15, $offset = 0 )
    {
        $result = $this->db->select( 'p.id,p.title,p.zone,p.amount,p.addtime,p.road,p.room,p.type,p.ispersonal,p.updatetime,c1.name as zonename,c2.name as roomname' )
                ->from( "{$this->_table['post']} as p" )
                ->join( "{$this->_table['common']} as c1", 'c1.id=p.zone' )
                ->join( "{$this->_table['common']} as c2", 'c2.id=p.room' )
                ->where( 'p.visible', 1 )
                ->where( 'p.ispersonal', 0 )
                ->where( 'p.type', $this->_postType['rent'] );
        if ( $highPrice === NULL )
        {
            $result->where( 'p.amount >=', $lowPrice );
        }
        else
        {
            $result->where( 'p.amount >=', $lowPrice )->where( 'p.amount <', $highPrice );
        }
        return $result->order_by( 'p.updatetime', 'desc' )
                        ->limit( $perPage, $offset )
                        ->get()
                        ->result_array();
    }
    
    /**
     * 根据金额范围获取所有中介出租信息
     * @param int $lowPrice
     * @param int $highPrice
     * @param int $perPage
     * @param int $offset
     * @return array
     */
    public function sellAgentByPrice( $lowPrice, $highPrice, $perPage = 15, $offset = 0 )
    {
        $result = $this->db->select( 'p.id,p.title,p.zone,p.amount,p.addtime,p.road,p.room,p.type,p.ispersonal,p.updatetime,c1.name as zonename,c2.name as roomname' )
                ->from( "{$this->_table['post']} as p" )
                ->join( "{$this->_table['common']} as c1", 'c1.id=p.zone' )
                ->join( "{$this->_table['common']} as c2", 'c2.id=p.room' )
                ->where( 'p.visible', 1 )
                ->where( 'p.ispersonal', 0 )
                ->where( 'p.type', $this->_postType['buy'] );
        if ( $highPrice === NULL )
        {
            $result->where( 'p.amount >=', $lowPrice );
        }
        else
        {
            $result->where( 'p.amount >=', $lowPrice )->where( 'p.amount <', $highPrice );
        }
        return $result->order_by( 'p.updatetime', 'desc' )
                        ->limit( $perPage, $offset )
                        ->get()
                        ->result_array();
    }

    /**
     * 根据厅室ID获取中介出租房源信息
     * @param int $roomId 厅室ID
     * @param int $perPage 每页显示条数
     * @param inte $offset 游标
     * @return array
     */
    public function rentAgentByRoomId( $roomId, $perPage = 15, $offset = 0 )
    {
        return $this->db->select( 'p.id,p.title,p.zone,p.amount,p.addtime,p.road,p.room,p.type,p.ispersonal,p.updatetime,c1.name as zonename,c2.name as roomname' )
                        ->from( "{$this->_table['post']} as p" )
                        ->join( "{$this->_table['common']} as c1", 'c1.id=p.zone' )
                        ->join( "{$this->_table['common']} as c2", 'c2.id=p.room' )
                        ->where( 'p.visible', 1 )
                        ->where( 'p.ispersonal', 0 )
                        ->where( 'p.room', $roomId )
                        ->where( 'p.type', $this->_postType['rent'] )
                        ->order_by( 'p.updatetime', 'desc' )
                        ->limit( $perPage, $offset )
                        ->get()
                        ->result_array();
    }

    /**
     * 根据厅室ID获取中介卖房源信息
     * @param int $roomId 厅室ID
     * @param int $perPage 每页显示条数
     * @param inte $offset 游标
     * @return array
     */
    public function sellAgentByRoomId( $roomId, $perPage = 15, $offset = 0 )
    {
        return $this->db->select( 'p.id,p.title,p.zone,p.amount,p.addtime,p.road,p.room,p.type,p.ispersonal,p.updatetime,c1.name as zonename,c2.name as roomname' )
                        ->from( "{$this->_table['post']} as p" )
                        ->join( "{$this->_table['common']} as c1", 'c1.id=p.zone' )
                        ->join( "{$this->_table['common']} as c2", 'c2.id=p.room' )
                        ->where( 'p.visible', 1 )
                        ->where( 'p.ispersonal', 0 )
                        ->where( 'p.room', $roomId )
                        ->where( 'p.type', $this->_postType['buy'] )
                        ->order_by( 'p.updatetime', 'desc' )
                        ->limit( $perPage, $offset )
                        ->get()
                        ->result_array();
    }
    
    /**
     * 根据关键字搜索出租房源信息
     * @param type $keyword
     * @param type $offset
     * @return type 
     */
    public function rentGetByKeyword( $keyword, $perPage = 15, $offset = 0 )
    {
        return $this->db->select( 'p.id,p.title,p.zone,p.amount,p.addtime,p.road,p.room,p.type,p.ispersonal,p.updatetime,c1.name as zonename,c2.name as roomname' )
                        ->from( "{$this->_table['post']} as p" )
                        ->join( "{$this->_table['common']} as c1", 'c1.id=p.zone' )
                        ->join( "{$this->_table['common']} as c2", 'c2.id=p.room' )
                        ->where( 'p.visible', 1 )
                        ->where( 'p.type', $this->_postType['rent'] )
                        ->like( 'p.title', $keyword )
                        ->order_by( 'p.updatetime', 'desc' )
                        ->limit( $perPage, $offset )
                        ->get()
                        ->result_array();
    }

    public function getByUserId( $userId, $perPage = 15, $offset = 0 )
    {
        return $this->db->select( 'p.id,p.title,p.zone,p.amount,p.addtime,p.road,p.room,p.type,p.ispersonal,p.updatetime,c1.name as zonename,c2.name as roomname' )
                        ->from( "{$this->_table['post']} as p" )
                        ->join( "{$this->_table['common']} as c1", 'c1.id=p.zone' )
                        ->join( "{$this->_table['common']} as c2", 'c2.id=p.room' )
                        ->where( 'p.userid', $userId )
                        ->order_by( 'p.updatetime', 'desc' )
                        ->limit( $perPage, $offset )
                        ->get()
                        ->result_array();
    }

    public function totalByUserId( $userId )
    {
        return $this->db->from( $this->_table['post'] )
                        ->where( 'userid', $userId )
                        ->count_all_results();
    }

    public function refreshByPostId( $postId )
    {
        $data = array(
            'updatetime' => time(),
        );
        $this->db->where('id', $postId)->update( $this->_table['post'], $data );
    }

    public function deleteByPostId( $postId )
    {
        $this->db->where('id', $postId)->delete($this->_table['post']);
    }
    
}

?>
